<? 
ob_start();
session_start();
		if(empty($HTTP_SESSION_VARS['user_login'])){
			echo"
			<tr>
			 <td align=center colspan=3>
			 <br><br><br>".$_SESSION['user_login']."
				Maaf, Anda tidak bisa mengakses halaman ini, Anda harus login terlebih dahulu ... <br><br>
				[ <a href='$path/index.php'>silahkan login</a> ]
			 </td>
			</tr>
			";
			exit();
		}
		
		include("../lib/config.php");
		include("../lib/function.php");
		include("../lib/header.php");
		
		$divisi 	= $HTTP_GET_VARS['divisi'];
		$st				= $HTTP_GET_VARS['st'];
		$stat_kar	= $HTTP_GET_VARS['stat_kar'];
		$jabatan	= $HTTP_GET_VARS['jabatan'];
		$pendidikan	= $HTTP_GET_VARS['pendidikan'];
		$golongan	= $HTTP_GET_VARS['golongan'];
		$bulan1		= $HTTP_GET_VARS['bulan1'];
		$bulan2		= $HTTP_GET_VARS['bulan2'];
		$tahun1		= $HTTP_GET_VARS['tahun1'];
		$tahun2		= $HTTP_GET_VARS['tahun2'];
		$jns_kursus	= $HTTP_GET_VARS['jns_kursus'];
		$excel		= $HTTP_GET_VARS['excel'];
		
	// cek hak akses untuk cabang
		$sad = mssql_query("SELECT nama_menu FROM VPrivUser A, VCabang B WHERE username='".$HTTP_SESSION_VARS['user_login']."' AND baca='1' AND A.nama_menu=B.Kode_Cabang",$conn);
		while($rad = mssql_fetch_array($sad)){
		// VARIABLE UNTUK CABANG
		$VAreaCode[] = $rad[0];
		}
/////////////////////////////////	
		
		if(empty($tahun1)){
			$tahun1 = 1980;
			$bulan1 = 1;
		}
		
		if(empty($tahun2)){
			$tahun2 = date("Y");
			$bulan2 = 12;
		}
		
		$tgl_awal 	= date("m",mktime(0,0,0,$bulan1,1,$tahun1))."/".date("t",mktime(0,0,0,$bulan1,1,$tahun1))."/".$tahun1;
		$tgl_akhir 	= date("m",mktime(0,0,0,$bulan2,1,$tahun2))."/".date("t",mktime(0,0,0,$bulan2,1,$tahun2))."/".$tahun2;

	?>
<title>Monitoring Kontrak Pegawai</title>
<?
	if($excel == "yes"){
	}
	else{
?>
<script type="text/javascript">
function Show_Excel(){
	var divisi 		= document.forms[0].elements['divisi'].value;
	//var stat_ker 	= document.forms[0].elements['stat_kar'].value;
	//var stat_kar 	= document.forms[0].elements['st'].value;
	//var jabatan		= document.forms[0].elements['jabatan'].value;
	//var pendidikan 	= document.forms[0].elements['pendidikan'].value;
	//var golongan	= document.forms[0].elements['golongan'].value;
	//var jns_kursus	= document.forms[0].elements['jns_kursus'].value;
	var tahun1 		= document.forms[0].elements['tahun1'].value;
	var tahun2 		= document.forms[0].elements['tahun2'].value;
	var bulan1		= document.forms[0].elements['bulan1'].value;
	var bulan2	 	= document.forms[0].elements['bulan2'].value;
//	var golongan	= document.forms[0].elements['golongan'].value;
	/*
	location.href = "<?$PHP_SELF?>?divisi="+divisi+"&st="+stat_kar+"&excel=yes&stat_kar="+stat_ker+"&pendidikan="+pendidikan+"&jabatan="+jabatan+"&golongan="+golongan+"&jns_kursus="+jns_kursus+"&tahun1="+tahun1+"&tahun2="+tahun2+"&bulan1="+bulan1+"&bulan2="+bulan2+"";
	*/
	location.href = "<?=$PHP_SELF?>?excel=yes&divisi="+divisi+"&tahun1="+tahun1+"&tahun2="+tahun2+"&bulan1="+bulan1+"&bulan2="+bulan2+"";
}	
</script>
<link rel="stylesheet" type="text/css" href="../style/style.css" />
 <div style="font-size:14px; border-bottom:solid 1px #000000; font-weight:bold;">Monitoring Kontrak Pegawai</div><br>
<fieldset>
 <legend>Filter Data</legend>
 <form action="<?=$PHP_SELF?>" method="get">
 <table border="0">
  <tr>
   <td>Divisi/Biro/Cabang</td>
   <td><select style='width:350px;' name='divisi'>
	    <option value='all'>--semua--</option>
        <?
		
		$sqljd  = "SELECT * FROM VLevelCabang WHERE status='1' ";
		$sqljd .= "AND AreaCode IN (";

		for($k=0;$k<count($VAreaCode);$k++){
			$sqljd .= "'$VAreaCode[$k]',";
		}

		$sqljd .= "'$VAreaCode[0]') ";		
		
		$sqljd .= " ORDER BY uraian ASC";
		
		$sq2 = mssql_query($sqljd,$conn);
		while($rq2 = mssql_fetch_array($sq2)){
			echo"<option value='".$rq2[kode_so]."'";
			if($divisi == $rq2[kode_so]){
				echo" selected";
			}
			echo">$rq2[uraian]</option>";
		}
		?>
        </select></td>
   <td align="right">&nbsp;</td>
   <td>&nbsp;</td>
  </tr>
  <tr>
   <td>Habis Masa Kontrak</td>
   <td colspan="3">
   <?
			 echo"<select name='bulan1'>
			  <option value=''";
			  if(empty($bulan1)){ echo" selected"; }
			  echo">--Bulan--</option>";
			  echo"
			   <option value='1'"; if($bulan1 == "1"){ echo" selected"; } else {} echo">Januari</option>
			   <option value='2'"; if($bulan1 == "2"){ echo" selected"; } else {} echo">Februari</option>
			   <option value='3'"; if($bulan1 == "3"){ echo" selected"; } else {} echo">Maret</option>
			   <option value='4'"; if($bulan1 == "4"){ echo" selected"; } else {} echo">April</option>
			   <option value='5'"; if($bulan1 == "5"){ echo" selected"; } else {} echo">Mei</option>
			   <option value='6'"; if($bulan1 == "6"){ echo" selected"; } else {} echo">Juni</option>
			   <option value='7'"; if($bulan1 == "7"){ echo" selected"; } else {} echo">Juli</option>
			   <option value='8'"; if($bulan1 == "8"){ echo" selected"; } else {} echo">Agustus</option>
			   <option value='9'"; if($bulan1 == "9"){ echo" selected"; } else {} echo">September</option>
			   <option value='10'"; if($bulan1 == "10"){ echo" selected"; } else {} echo">Oktober</option>
			   <option value='11'"; if($bulan1 == "11"){ echo" selected"; } else {} echo">November</option>
			   <option value='12'"; if($bulan1 == "12"){ echo" selected"; } else {} echo">Desember</option>
			 </select>
			 <select name='tahun1'>
			  <option value=''>--Tahun--</option>";
				for($t1=1980;$t1<=2100;$t1++){
					echo"<option value='$t1'";
					if($tahun1 == $t1){
						echo" selected";
					}
					echo">$t1</option>";
				}
			  echo"
			 </select>
			 s.d
			 <select name='bulan2'>
			  <option value=''";
			  if(empty($bulan2)){ echo"selected"; }			  
			  echo">--Bulan--</option>
			   <option value='1'"; if($bulan2 == "1"){ echo" selected"; } else {} echo">Januari</option>
			   <option value='2'"; if($bulan2 == "2"){ echo" selected"; } else {} echo">Februari</option>
			   <option value='3'"; if($bulan2 == "3"){ echo" selected"; } else {} echo">Maret</option>
			   <option value='4'"; if($bulan2 == "4"){ echo" selected"; } else {} echo">April</option>
			   <option value='5'"; if($bulan2 == "5"){ echo" selected"; } else {} echo">Mei</option>
			   <option value='6'"; if($bulan2 == "6"){ echo" selected"; } else {} echo">Juni</option>
			   <option value='7'"; if($bulan2 == "7"){ echo" selected"; } else {} echo">Juli</option>
			   <option value='8'"; if($bulan2 == "8"){ echo" selected"; } else {} echo">Agustus</option>
			   <option value='9'"; if($bulan2 == "9"){ echo" selected"; } else {} echo">September</option>
			   <option value='10'"; if($bulan2 == "10"){ echo" selected"; } else {} echo">Oktober</option>
			   <option value='11'"; if($bulan2 == "11"){ echo" selected"; } else {} echo">November</option>
			   <option value='12'"; if($bulan2 == "12"){ echo" selected"; } else {} echo">Desember</option>
			 </select>
			 <select name='tahun2'>
			  <option value=''>--Tahun--</option>";
				for($t2=1980;$t2<=2100;$t2++){
					echo"<option value='$t2'";
					if($tahun2 == $t2){
						echo" selected";
					}
					echo">$t2</option>";
				}
			  echo"</select>";
			  ?>
   </td>
  </tr>
  <tr>
   <td>&nbsp;</td>
   <td><input type="submit" class="button" value="Cari"><input type="button" value="Kirim ke Excel" onclick="Javascript:Show_Excel();" class="button" /></td>
  </tr>
 </table>
 </form> 
</fieldset>
<?
// end if xecle
}

	$sqlcount = "SELECT A.* FROM VMonKontrak A, vlevelcabang B WHERE stat_kar2 IN ('2','3','4','5') AND Jenis NOT IN ('U','H') AND Jenis IN ('R','G') AND A.kode_so2=B.kode_so ";
	
	$sqlcount .= "AND B.AreaCode IN (";

	for($k=0;$k<count($VAreaCode);$k++){
		$sqlcount .= "'$VAreaCode[$k]',";
	}

	$sqlcount .= "'$VAreaCode[0]') ";		

	
	/*
	if($bulan2+1 == 13){
		$bulanke2 = "01";
	}
	else{
		$bulanke2 = ($bulan2+1);
	}

	if($bulan1+1 == 13){
		$bulanke1 = "01";
	}
	else{
		$bulanke1 = ($bulan1+1);
	}
	*/
	
	// cek bulan dan tahun tanggal habis kontrak 
	if(!empty($bulan1) && !empty($tahun1) && !empty($bulan2) && !empty($tahun2)){
		$sqlcount .= " and tgl_selesai between convert(datetime,'".$tgl_awal."') and convert(datetime,'".$tgl_akhir."')";
	}
	else{	
		if(!empty($bulan1) && !empty($tahun1) && empty($bulan2) && empty($tahun2)){
			$sqlcount .= " and tgl_selesai between convert(datetime,'".$tgl_awal."') and convert(datetime,'".$tgl_akhir."') ";
		}
		elseif(!empty($bulan2) && !empty($tahun2) && empty($bulan1) && empty($tahun1)){
			$sqlcount .= " and tgl_selesai between convert(datetime,'".$tgl_awal."') and convert(datetime,'".$tgl_akhir."') ";
		}	
	}
	// divisi
	if(!empty($divisi)){
		if($divisi == "all"){
		}
		else{
			$sqlcount .= " AND substring(kode_so2,1,7)='".substr($divisi,0,7)."'";
		}
	}
			
//echo $sqlcount;
$result	= mssql_query($sqlcount,$conn);
//mssql_free_result($result,0,0);
$total	= mssql_num_rows($result);
$limit	= 20;
$pager  = Pager::getPagerData($total, $limit, $page);
$offset = $pager->offset;
$limit  = $pager->limit;
$page   = $pager->page;

if($total > 0)
{
	$sql = "WITH MONKONTRAK AS (SELECT ROW_NUMBER() OVER(ORDER BY NIK) AS No, CONVERT(CHAR(12), tgl_selesai, 103) TglSelesai, CONVERT(CHAR(12), tgl_skd, 103) TglSKD, CONVERT(CHAR(12), tgl_efektif, 103) TglEfektif, A.* FROM VMonKontrak A, vlevelcabang B WHERE stat_kar2 IN ('2','3','4','5') AND Jenis NOT IN ('U','H') AND Jenis IN ('R','G') AND A.kode_so2=B.kode_so ";
	
	$sql .= "AND B.AreaCode IN (";

	for($k=0;$k<count($VAreaCode);$k++){
		$sql .= "'$VAreaCode[$k]',";
	}

	$sql .= "'$VAreaCode[0]') ";		

	/*
	if($bulan2+1 == 13){
		$bulanke2 = "01";
	}
	else{
		$bulanke2 = ($bulan2+1);
	}

	if($bulan1+1 == 13){
		$bulanke1 = "01";
	}
	else{
		$bulanke1 = ($bulan1+1);
	}
	*/
	// cek bulan dan tahun tanggal habis kontrak 
	
	if(!empty($bulan1) && !empty($tahun1) && !empty($bulan2) && !empty($tahun2)){
		$sql .= " and tgl_selesai between convert(datetime,'".$tgl_awal."') and convert(datetime,'".$tgl_akhir."')";
	}
	else{	
		if(!empty($bulan1) && !empty($tahun1) && empty($bulan2) && empty($tahun2)){
			$sql .= " and tgl_selesai between convert(datetime,'".$tgl_awal."') and convert(datetime,'".$tgl_akhir."') ";
		}
		elseif(!empty($bulan2) && !empty($tahun2) && empty($bulan1) && empty($tahun1)){
			$sql .= " and tgl_selesai between convert(datetime,'".$tgl_awal."') and convert(datetime,'".$tgl_akhir."') ";
		}	
	}
	// divisi
	if(!empty($divisi)){
		if($divisi == "all"){
		}
		else{
			$sql .= " AND substring(kode_so2,1,7)='".substr($divisi,0,7)."'";
		}
	}

	$sql .= ") SELECT * FROM MONKONTRAK ";
	/// end query	
	
	if($excel == "yes"){
	
	}
	else{
		$sql .= " WHERE No BETWEEN ".$offset." AND ".($offset+$limit)." order by tgl_selesai";
	}
	
	$query = mssql_query($sql,$conn);
	
	if($excel == "yes"){
	
	}
	else{
		PagingLapTr($pager,$page,$next,$limit,$keyword,$show,$conn,$st,$divisi,$stat_kar,$pendidikan,$jabatan,$golongan,$bulan1,$bulan2,$tahun2,$tahun1,$jns_kursus);
	}
	
	if($excel == "yes"){
	
	}
	else{
?>
<fieldset>
 <legend>Hasil Pencarian</legend>
<?
	}
?>
 <table width="100%">
  <tr class="headlist">
   <td>No.</td>
   <td>NIK</td>
   <td>Nama</td>
   <td>No. Surat</td>
   <td>Jumlah Kontrak</td>
   <td>Tgl Efektif</td>
   <td>Tgl SKD</td>
   <td>Tgl Selesai</td>
   <td>Divisi/Biro/Cabang</td>
   <td>Jabatan</td>
   <td>Gol.</td>
   <td>Status Karyawan</td>
  </tr>
  <?
  while($row = mssql_fetch_array($query)){
  ?>
  <tr class="contentlist" style="font-size:smaller;">
   <td align="right"><?=$row[No]?></td>
   <td><?=$row[NIK]?></td>
   <td><?=$row[Nama]?></td>
   <td><?=$row[no_surat]?></td>
   <td align="center">
   <?
   // jumlah kontrak pegawai
   $qss = mssql_query("SELECT count(*) FROM KarirPTSI WHERE NIK='$row[NIK]' AND Jenis IN ('R','G')",$conn);
   $rss = mssql_fetch_array($qss);
   echo"$rss[0]";
   ?>
   </td>
   <td><?=$row[TglEfektif]?></td>
   <td><?=$row[TglSKD]?></td>
   <td style="color:blue;"><?=$row[TglSelesai]?></td>
   <td>
   <?
   // get divisi
	$kode_so = $row[kode_so2];
	if(empty($kode_so)){
		
	}
	else{
		$sdiv = mssql_query("SELECT uraian FROM VLevelCabang WHERE substring(kode_so,1,7)='".substr($kode_so,0,7)."'");
		$rdiv = mssql_fetch_array($sdiv);
		echo"$rdiv[0]";
	}
   ?>
   </td>
   <td>
   <?
   // get jabatan
   	if(empty($row[jabatan2])){
	
	}
	else{
		$jabatan = $row[jabatan2];
		$sjab	= mssql_query("SELECT Jabatan FROM MstJabatan WHERE kode='$jabatan'",$conn);
		$rjab	= mssql_fetch_array($sjab);
		echo"$rjab[0]";
	}
   ?>
   </td>
   <td><?=$row[gol2]?></td>
   <td>
   <?
   // status karyawan
   	if(empty($row[stat_kar2])){
	
	}
	else{
		$stat_kar2 = $row[stat_kar2];
		$sstat_kar2	= mssql_query("SELECT Nama FROM VStatusKaryawan WHERE kode='$stat_kar2'",$conn);
		$rstat_kar2	= mssql_fetch_array($sstat_kar2);
		echo"$rstat_kar2[0]";
	}
   ?>
   </td>
  </tr>
  <?
  }
  ?>
 </table>
 <?
 	if($excel == "yes"){
		header('Content-type: application/vnd.ms-excel');
		header("Content-Disposition: attachment; filename=LaporanMonitoringKontrakPegawai".date("dmY").".xls");
		header("Pragma: no-cache");
		header("Expires: 0");
	}
	else{
 ?>
</fieldset>
<?
	}
}
else{
	echo"<br><br><div align=center>Data Tidak Ditemukan</div>";
}
//ob_clean();
?>